package com.portal.dao;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.portal.dto.PacienteDTO;
import com.portal.vo.PacienteVO;


import oracle.jdbc.OracleTypes;
import utiles.ConexionPool;
import utiles.AppProperties;
import utiles.utilitarios;

//import java.io.OutputStream;

public class PacienteServicioDAO {
	private static Logger log = Logger.getLogger(PacienteServicioDAO.class);
	String pkgVar = (AppProperties.getProperty("aplicacion", "PACKAGE"));
	String horaActual = utilitarios.getHoraActual();
	
	public  List<PacienteDTO> getPaciente(String rut, String idPac, String idUser) throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
    	List<PacienteDTO> listPaciente = new ArrayList<PacienteDTO>();
		try {
			//System.out.println("se envia rut:"+rut+" idPac:"+idPac+" idUser:"+idUser);
			String query = "{ call "+pkgVar+".Pkg_pac_paciente.getPaciente(?,?,?,?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.setString(1, rut);
			stmt.setString(2, idPac);
			stmt.setString(3, idUser);
			stmt.registerOutParameter(4, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(4);
			
			while (rs.next()) {
				//System.out.println("recibe idpac:"+rs.getString(1));
				PacienteDTO listpac = new PacienteDTO();
				listpac.setIdPaciente(rs.getString(1));
				listpac.setIdPais(rs.getString(2));
				listpac.setIdComuna(rs.getString(3));
				listpac.setRut(rs.getString(4));
				listpac.setDv(rs.getString(5));
				listpac.setNombre(rs.getString(6));
				listpac.setApePaterno(rs.getString(7));
				listpac.setApeMaterno(rs.getString(8));
				listpac.setFecNac(rs.getString(9));
				listpac.setDireccion(rs.getString(10));
				listpac.setPoblacion(rs.getString(11));
				listpac.setTelefonos(rs.getString(12));
				listpac.setEmail(rs.getString(13));	
				listpac.setFonoMovil(rs.getString(14));
				listpac.setActivo(rs.getString(15));
				listpac.setIdRegion(rs.getString(16));		
				listpac.setSexo(rs.getString(17));
				listpac.setEdad(rs.getString(18));
				listpac.setComuna(rs.getString(19));
				listpac.setRegion(rs.getString(20));
				listpac.setFotoTamano(rs.getString(21));
				listpac.setNombreCompleto(rs.getString(22));
				listpac.setFecIngreso(rs.getString(23));
				// ** Datos de SM Ficha Clinica
				listpac.setIdPrevision(rs.getString(24));
				//System.out.println("rs.getString(24) prevision social:"+rs.getString(24));
				listpac.setObservaciones(rs.getString(25));
				listpac.setFecVisita(rs.getString(26));
				listpac.setCrisis(rs.getString(27));
				listpac.setIdPrevisionEEG(rs.getString(28));
				//System.out.println("rs.getString(28) eeg: "+rs.getString(28));
				listpac.setPrevisionEEG(rs.getString(29));
				listpac.setIdTipoAtencion(rs.getString(30));
				listpac.setGratuidadSM(rs.getString(31));
				listPaciente.add(listpac);
			}
			
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error("Error PacienteServicioDAO.getPacienteByRut SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {
			//ConexionPool.desconectaPool(conn);
			conn.close();			
            /*if (conn.isClosed()){
				System.out.println("conexion cerrada en getPaciente");
			}else{
				System.out.println("conexion NO cerrada en getPaciente");
			}*/
            conn = null;
			
		}
		return listPaciente;	
	}
	
	
	public  List<PacienteDTO> getAllPaciente(String buscar, String idArea) throws SQLException, IOException{
		//ConexionPool connDao = new ConexionPool();
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
    		
		List<PacienteDTO> listPaciente = new ArrayList<PacienteDTO>();
		try {
			String query = "{ call "+pkgVar+".Pkg_pac_paciente.getAllPaciente(?,?,?) }"; 	       
			CallableStatement  stmt = conn.prepareCall(query);
			stmt.setString(1, buscar);
			stmt.setString(2, idArea);
			stmt.registerOutParameter(3, OracleTypes.CURSOR);
			stmt.execute();
			ResultSet rs = (ResultSet)stmt.getObject(3);
			while (rs.next()) {
				PacienteDTO listpac = new PacienteDTO();
				listpac.setIdPaciente(rs.getString(1));
				listpac.setRut(rs.getString(2));
				listpac.setDv(rs.getString(3));
				listpac.setNombreCompleto(rs.getString(4));
				listPaciente.add(listpac);
			}
			rs.close();
			stmt.close();
		} catch (SQLException ex) {  
			log.error("Error PacienteServicioDAO.getPacienteByRut SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
		} finally {
			//ConexionPool.desconectaPool(conn);
			conn.close();
			conn = null;
		}
		return listPaciente;	
	}	
	public String setPaciente(PacienteVO pacienteVO, byte[] bytesImg) throws SQLException, IOException{
	
		//ConexionPool connDao = new ConexionPool();		
		Connection conn = ConexionPool.getConnectionPool();	
    	//Connection conn = ((javax.sql.PooledConnection)ConexionPool.getConnectionPool()).getConnection();
    	
		String resultadoIns = "error";		
		try {			
			String query = "{ call "+pkgVar+".PKG_PAC_PACIENTE.setPaciente(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}"; 	
			CallableStatement  stmt = conn.prepareCall(query);			
			stmt.setString(1,pacienteVO.getIdPpais());		
			stmt.setString(2,pacienteVO.getIdComuna());
			stmt.setString(3,pacienteVO.getRut());
			stmt.setString(4,pacienteVO.getDigitoRut());
			stmt.setString(5,pacienteVO.getNombre());
			stmt.setString(6,pacienteVO.getApellidoP());
			stmt.setString(7,pacienteVO.getApellidoM());
			stmt.setString(8,pacienteVO.getFechaNac());
			stmt.setString(9,pacienteVO.getDireccion());
			stmt.setString(10,pacienteVO.getPoblacion());
			stmt.setString(11,pacienteVO.getTelefonos());
			stmt.setString(12,pacienteVO.getCorreoElectronico());
			stmt.setString(13,pacienteVO.getTelefonosMoviles());
			stmt.setString(14,"1");
			stmt.setString(15,"1");
			stmt.setString(16,"1");
			stmt.setString(17,pacienteVO.getActivoSN());
			stmt.setString(18,pacienteVO.getUsuarioCambio());
			stmt.setString(19,pacienteVO.getSexo());			
			if (bytesImg.length==0){
				stmt.setBinaryStream(20,null,0);
			}else{
				stmt.setBinaryStream(20,new ByteArrayInputStream(bytesImg),1000000);
			}
			//stmt.setString(20," ");
			stmt.setString(21,pacienteVO.getObs());	
			stmt.setString(22,pacienteVO.getPrevision());	
			stmt.setString(23, pacienteVO.getIdServicio());
			stmt.setString(24, pacienteVO.getPrevisionEEG());
			//System.out.println("Area:"+pacienteVO.getIdServicio()+"; SME:"+pacienteVO.getPrevision()+"; EEG:"+pacienteVO.getPrevisionEEG());
			stmt.registerOutParameter(25, OracleTypes.VARCHAR);
			stmt.execute();		
			resultadoIns =stmt.getString(25);
            stmt.close();
		} catch (SQLException ex) {  
			log.error("Error PacienteServicioDAO.setAddPaciente SQLException: " +ex.getMessage());
			System.out.println(ex.getMessage());
			return resultadoIns;
		} finally {
			//ConexionPool.desconectaPool(conn);
			conn.close();			
            if (conn.isClosed()){
				//System.out.println("conexion cerrada en set Paciente");
			}else{
				System.out.println("conexion NO cerrada en set Paciente");
			}
            conn = null;
		}
		//System.out.println("resultadoIns>"+resultadoIns);
		if(resultadoIns.equals("I")){
			//System.out.println("Se Inserto paciente RUT:"+pacienteVO.getRut()+"-"+pacienteVO.getDigitoRut());
			//log.info("Se Insert� paciente RUT:"+pacienteVO.getRut()+"-"+pacienteVO.getDigitoRut());			
		}else if (resultadoIns.equals("U")){
			//System.out.println("Se Actualizo paciente RUT:"+pacienteVO.getRut()+"-"+pacienteVO.getDigitoRut());
			//log.info("Se Actualiz� paciente RUT:"+pacienteVO.getRut()+"-"+pacienteVO.getDigitoRut());			
		}
		
		return resultadoIns;
	}
}